const SqliteUtil = appRequire('modules/SqliteUtil');
const PageResultVo = appRequire('vo/PageResultVo.js');
class SysAuthSqliteDao {
  constructor() {

  }
  static async create() {
    let db = await SqliteUtil.open(appConfig.dbPath);
    await db.run(`CREATE TABLE "sys_auth" (
      "id" VARCHAR(64) NOT NULL,
      "pid" VARCHAR(64) DEFAULT '',
      "path" VARCHAR(4096) DEFAULT '',
      "auth_name" VARCHAR(255) DEFAULT '',
      "auth_url" VARCHAR(255) DEFAULT '',
      "auth_type"  VARCHAR(64) DEFAULT '',
      "auth_icon" VARCHAR(255) DEFAULT '',
      "remarks" VARCHAR(255) DEFAULT '',
      "create_time" VARCHAR(255) DEFAULT '',
      "update_time" VARCHAR(255) DEFAULT '',
      "is_used" VARCHAR(10) DEFAULT '0',
      "is_hide" VARCHAR(10) DEFAULT '0',
      "sort" integer DEFAULT 0,
      PRIMARY KEY ("id")
    );`);
    await db.close();
  }
  static async saveOne(auth) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      await db.run(`insert into sys_auth
          (id,pid,path,auth_name,auth_url,auth_type,auth_icon,remarks,create_time,update_time,sort) 
          values(?,?,?,?,?,?,?,?,?,?,?)`,
        [auth.id,auth.pid,auth.path,auth.authName,auth.authUrl,auth.authType,auth.authIcon,auth.remarks,auth.createTime, auth.updateTime,auth.sort]);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async updateOne(auth) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      await db.run(`update sys_auth 
        set pid=?,path=?,auth_name=?,auth_url=?,auth_type=?,auth_icon=?,remarks=?,update_time=?,is_used=?,is_hide=?,sort=? where id = ?`,
      [auth.pid,auth.path,auth.authName, auth.authUrl,auth.authType,auth.authIcon,auth.remarks, auth.updateTime, auth.isUsed, auth.isHide, auth.sort,auth.id]);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async deleteById(id) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      await db.run(`delete from sys_auth where id = ?`,
      [id]);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async deleteMany(ids) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      console.log(ids);
      let sqlWhere = 'where id in (';
      for(let i = 0;i<ids.length;i++){
        if(i!=ids.length-1){
          sqlWhere+=`'${ids[i]}',`
        }else{
          sqlWhere+=`'${ids[i]}')`
        }
      }
      let sql = `delete from sys_auth ${sqlWhere}`;
      console.log(sql);
      await db.run(sql);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findOne(id) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      let data = await db.get(`select 
      c.id,c.path,c.pid,c.auth_name as authName,c.auth_type as authType,
      t.type_name as authTypeName,c.auth_url as authUrl,c.auth_icon as authIcon,c.sort 
      from sys_auth c left join sys_auth_type t on c.auth_type = t.id 
      where c.id=?`,[id]);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findById(id) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      let data = await db.get(`select * from sys_auth where id=?`,[id]);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findAll() {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      let data = await SqliteUtil.each(db,`select sa.id,sa.pid,sa.auth_name as authName,sa.path,
      st.type_name as authTypeName,sa.auth_url as authUrl,sa.auth_icon as authIcon,sa.sort,sa.create_time as createTime from sys_auth sa left join sys_auth_type st on sa.auth_type=st.id order by pid,sa.sort desc,sa.create_time desc`);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findByPage(page) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      let sqlWhere = '';
      let sqlParams = [(page.pageNum-1)*page.pageSize,page.pageSize];
      if(page.searchParams&&page.searchParams.authName){
        sqlWhere +='where auth_name=?'
        sqlParams.unshift(page.searchParams.authName);
      }
      let result = await db.get(`select count(*) as totalNum from sys_auth sa left join sys_auth_type st on sa.auth_type=st.id ${sqlWhere}`);
      let count = result.totalNum;
      let data = await SqliteUtil.each(db,`select sa.id,sa.pid,sa.auth_name as authName,st.type_name as authTypeName,sa.auth_url as authUrl,sa.auth_icon as authIcon,sa.sort,sa.create_time as createTime from sys_auth sa left join sys_auth_type st on sa.auth_type=st.id ${sqlWhere} order by pid,sort desc,create_time desc limit ?,?`,sqlParams);
      //加了await 会等待data拼装完成
      return PageResultVo.success('success',count,data);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }   
  }
}
module.exports = SysAuthSqliteDao;